﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using jiaju.Areas.Back.Models.DBModel;
using jiaju.Areas.Back.Models;
using System.Data.SqlClient;
namespace jiaju.Areas.Back.Models.DAL
{
    public class DesignerService
    {
        //获取表格并且分页
        public static List<Designer> getDesigner(int page, int limit, string name)
        {
            String sql = string.Format("select top({0}) * from dbo.designer where Id not in (select top(({1} - 1) * {0}) Id from designer)", limit, page);
            //通过sql查询数据
            if (!string.IsNullOrEmpty(name))
            {
                //如果title不为空，并且不为null，拼接上and Name like '%title%'
                sql = sql + string.Format("and Name like '%{0}%'", name);
            }
            SqlDataReader sdr = DBHelp.ExecuteReader(sql);
            List<Designer> list = new List<Designer>();
            while(sdr.Read())
            {
                int Id = (int)sdr["Id"];
                string Name = (string)sdr["Name"];
                string ImgUrl = (string)sdr["ImgUrl"];
                string Position = (string)sdr["Position"];
                Designer des = new Designer()
                {
                    Id=Id,
                    Name=Name,
                    ImgUrl = ImgUrl,
                    Position=Position
                };
                list.Add(des);
            }
            return list;
        }
        //新增设计师
        public static int addDesigner(Designer designer)
        {
            string sql = string.Format(@"INSERT INTO [dbo].[designer]
                               (ImgUrl ,Name,Position)
                         VALUES( '{0}', '{1}' ,'{2}')", designer.ImgUrl, designer.Name, designer.Position);
            int num = DBHelp.ExecuteNonQuery(sql);
            return num;
        }
        //通过Id获取详情
        public static Designer GetById(string ArtId)
        {
            string sql = "select * from dbo.designer where Id=" + ArtId;
            Designer designer = null;
            SqlDataReader sdr = DBHelp.ExecuteReader(sql);
            while (sdr.Read())
            {
                int Id = (int)sdr["Id"];
                string Name = (string)sdr["Name"];
                string ImgUrl = (string)sdr["ImgUrl"];
                string Position = (string)sdr["Position"];
                designer = new Designer()
                {
                    Id=Id,
                    Name = Name,
                    ImgUrl = ImgUrl,
                    Position=Position
                };
            }
            return designer;
        }
        //编辑
        public static int EditDesigner(Designer des)
        {
            string sql = String.Format(@"UPDATE [dbo].[designer]
                                  SET Name ='{0}'
                                  ,ImgUrl='{1}'
                                  ,Position='{2}'
                WHERE Id={3}", des.Name, des.ImgUrl, des.Position, des.Id);
            int num = DBHelp.ExecuteNonQuery(sql);
            return num;
        }
        //删除
        public static int DelDesigner(String Id)
        {
            String sqlPhoto = "delete from dbo.designer where Id=" + Id;
            int num = DBHelp.ExecuteNonQuery(sqlPhoto);
            return num;
        }
        //获取所有设计师的的名字
        public static List<Designer> getAllTitle()
        {
            string sql = "select Id,Name,Position from dbo.designer";
            SqlDataReader sdr = DBHelp.ExecuteReader(sql);
            List<Designer> list = new List<Designer>();
            while (sdr.Read())
            {
                int id = (int)sdr["Id"];
                string name= (string)sdr["Name"];
                string position = (string)sdr["Position"];
                Designer arc = new Designer() { Id = id, Name = name,Position=position };
                list.Add(arc);
            }
            return list;
        }
    }
}